package com.threeidiots.findit.db;

import com.threeidiots.findit.R;

import android.content.ContentResolver;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteCursor;
import android.database.sqlite.SQLiteCursorDriver;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteQuery;
import android.provider.MediaStore.Images.Media;
import android.provider.MediaStore.Images.Thumbnails;
import android.util.Log;

/**
 * Database class for Model use
 * @author Hemslo
 *
 */
public class FindItDatabase extends SQLiteOpenHelper{
	private final Context mContext;

	public FindItDatabase(Context context) {
		super(context, context.getResources().getString(R.string.db_name),
				null, Integer.valueOf(context.getResources().getString(
						R.string.db_version)));
		this.mContext = context;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		String[] sql = mContext.getString(R.string.FindItDatabase_onCreate)
				.split("\n");
		db.beginTransaction();
		try {
			// Create tables
			execMultipleSQL(db, sql);
			db.setTransactionSuccessful();
		} catch (SQLException e) {
			Log.e("Error creating tables and debug data", e.toString());
		} finally {
			db.endTransaction();
		}
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		String[] sql = mContext.getString(R.string.FindItDatabase_onUpgrade)
				.split("\n");
		db.beginTransaction();
		try {
			// Create tables
			execMultipleSQL(db, sql);
			db.setTransactionSuccessful();
		} catch (SQLException e) {
			Log.e("Error creating tables and debug data", e.toString());
		} finally {
			db.endTransaction();
		}

		onCreate(db);
	}

	private void execMultipleSQL(SQLiteDatabase db, String[] sql) {
		for (String s : sql)
			if (s.trim().length() > 0)
				db.execSQL(s);
	}

	public static class RecordsCursor extends SQLiteCursor {

		public RecordsCursor(SQLiteDatabase db, SQLiteCursorDriver driver,
				String editTable, SQLiteQuery query) {
			super(db, driver, editTable, query);
		}

		private static class Factory implements SQLiteDatabase.CursorFactory {

			public Cursor newCursor(SQLiteDatabase db,
					SQLiteCursorDriver driver, String editTable,
					SQLiteQuery query) {
				return new RecordsCursor(db, driver, editTable, query);
			}

		}

		public long getColRecordsId() {
			return getLong(getColumnIndexOrThrow("_id"));
		}

		public String getColThing() {
			return getString(getColumnIndexOrThrow("thing"));
		}

		public Double getColLatitude() {
			double latitude = getDouble(getColumnIndexOrThrow("latitude"));
			if (latitude == 0) {
				return null;
			}
			return latitude;
		}

		public Double getColLongitude() {
			double longitude = getDouble(getColumnIndexOrThrow("longitude"));
			if (longitude == 0) {
				return null;
			}
			return longitude;

		}

		public String getColNote() {
			return getString(getColumnIndexOrThrow("note"));
		}

		public String getColTag() {
			return getString(getColumnIndexOrThrow("tag"));
		}

		public String getColPicture() {
			return getString(getColumnIndexOrThrow("picture"));
		}

		public String getColTape() {
			return getString(getColumnIndexOrThrow("tape"));
		}

		public String getColPlaceTime() {
			return getString(getColumnIndexOrThrow("place_time"));
		}

		public String getColPickTime() {
			return getString(getColumnIndexOrThrow("pick_time"));
		}

		public int getColHistory() {
			return this.getInt(getColumnIndexOrThrow("history"));
		}
	}

	/**
	 * SELECT * FROM records
	 * @param history Determine whether return history
	 * @return
	 */
	public RecordsCursor getRecords(boolean history) {
		String sql = "SELECT * FROM records WHERE history="
				+ (history ? "1" : "0");
		SQLiteDatabase d = getReadableDatabase();
		RecordsCursor c = (RecordsCursor) d.rawQueryWithFactory(
				new RecordsCursor.Factory(), sql, null, null);
		c.moveToFirst();
		return c;
	}
	
	/**
	 * 
	 * @param pattern String to search
	 * @param history Determine whether return history
	 * @return
	 */
	public RecordsCursor searchReords(String pattern, boolean history) {
		String sql = "SELECT * FROM records WHERE history="+(history?"1":"0")
				+ "AND (thing LIKE ? OR note LIKE ? OR tag LIKE ?)";
		String pat = "%" + pattern + "%";
		String[] whereArgs = {pat, pat, pat};
		SQLiteDatabase d = getReadableDatabase();
		RecordsCursor c = (RecordsCursor) d.rawQueryWithFactory(
				new RecordsCursor.Factory(), sql, whereArgs, null);
		c.moveToFirst();
		return c;
	}

	public void addRecord(String thing, Double latitude, Double longitude,
			String note, String tag, String picture, String tape,
			String place_time, String pick_time, int history) {
		ContentValues map = new ContentValues();
		map.put("thing", thing);
		map.put("latitude", latitude);
		map.put("longitude", longitude);
		map.put("note", note);
		map.put("tag", tag);
		map.put("picture", picture);
		map.put("tape", tape);
		map.put("place_time", place_time);
		map.put("pick_time", pick_time);
		map.put("history", history);
		getWritableDatabase().insert("records", null, map);
	}

	public void editRecord(long _id, String thing, Double latitude,
			Double longitude, String note, String tag, String picture,
			String tape, String place_time, String pick_time, int history) {
		ContentValues map = new ContentValues();
		map.put("thing", thing);
		map.put("latitude", latitude);
		map.put("longitude", longitude);
		map.put("note", note);
		map.put("tag", tag);
		map.put("picture", picture);
		map.put("tape", tape);
		map.put("place_time", place_time);
		map.put("pick_time", pick_time);
		map.put("history", history);
		String[] whereArgs = new String[] { Long.toString(_id) };
		getWritableDatabase().update("records", map, "_id=?", whereArgs);
	}

	/**
	 * 
	 * @param _id id of record be deleted, 0 to delete all
	 */
	public void deleteRecord(long _id) {
		if (_id == 0) {
			getWritableDatabase().delete("records", null, null);
		} else {
			String[] whereArgs = new String[] { Long.toString(_id) };
			getWritableDatabase().delete("records", "_id=?", whereArgs);
		}
	}
	
	public void clearRecord(boolean history) {
		getWritableDatabase().delete("records", "history="
				+ (history ? "1" : "0"), null);
	}

	public int getRecordsCount(boolean history) {
		Cursor c = null;
		String sql = "SELECT count(*) FROM records WHERE history="
				+ (history ? "1" : "0");
		try {
			c = getReadableDatabase().rawQuery(sql, null);
			if (0 >= c.getCount()) {
				return 0;
			}
			c.moveToFirst();
			return c.getInt(0);
		} finally {
			if (null != c) {
				try {
					c.close();
				} catch (SQLException e) {
				}
			}
		}
	}

	public static class TagsCursor extends SQLiteCursor {

		public TagsCursor(SQLiteDatabase db, SQLiteCursorDriver driver,
				String editTable, SQLiteQuery query) {
			super(db, driver, editTable, query);
		}

		private static class Factory implements SQLiteDatabase.CursorFactory {

			public Cursor newCursor(SQLiteDatabase db,
					SQLiteCursorDriver driver, String editTable,
					SQLiteQuery query) {
				return new TagsCursor(db, driver, editTable, query);
			}

		}

		public long getColTagsId() {
			return getLong(getColumnIndexOrThrow("_id"));
		}

		public String getColTagsTag() {
			return getString(getColumnIndexOrThrow("tag"));
		}
	}

	public TagsCursor getTags() {
		String sql = "SELECT * FROM tags";
		SQLiteDatabase d = getReadableDatabase();
		TagsCursor c = (TagsCursor) d.rawQueryWithFactory(
				new TagsCursor.Factory(), sql, null, null);
		c.moveToFirst();
		return c;
	}

	public void addTag(String tag) {
		ContentValues map = new ContentValues();
		map.put("tag", tag);
		getWritableDatabase().insert("tags", null, map);
	}

	public void editTag(long _id, String tag) {
		ContentValues map = new ContentValues();
		map.put("tag", tag);
		String[] whereArgs = new String[] { Long.toString(_id) };
		getWritableDatabase().update("tags", map, "_id=?", whereArgs);
	}

	public void deleteTag(long _id) {
		if (_id == 0) {
			getWritableDatabase().delete("tags", null, null);
		} else {
			String[] whereArgs = new String[] { Long.toString(_id) };
			getWritableDatabase().delete("tags", "_id=?", whereArgs);
		}
	}

	public int getTagsCount() {
		Cursor c = null;
		String sql = "SELECT count(*) FROM tags";
		try {
			c = getReadableDatabase().rawQuery(sql, null);
			if (0 >= c.getCount()) {
				return 0;
			}
			c.moveToFirst();
			return c.getInt(0);
		} finally {
			if (null != c) {
				try {
					c.close();
				} catch (SQLException e) {
				}
			}
		}
	}
	
	public String getThumbnail(String original) {
		if (original == null) {
			return null;
		}
		ContentResolver cr = this.mContext.getContentResolver();
		String[] projection = {Media._ID};
		String[] selectionArgs = {original};
		Cursor cursor = cr.query(Media.EXTERNAL_CONTENT_URI, projection,
				Media.DATA + "=?", selectionArgs, null);
		
		if (cursor == null) {
			return null;
		}
		
		cursor.moveToFirst();
		if (cursor.isAfterLast()) {
			return null;
		}
		String image_id = cursor.getString(cursor.getColumnIndexOrThrow(Media._ID));
		if (image_id == null) {
			return null;
		}
		String[] projection2 = {Thumbnails.DATA};
		String[] selectionArgs2 = {image_id};
		cursor = cr.query(Thumbnails.EXTERNAL_CONTENT_URI, projection2,
				Thumbnails.IMAGE_ID + "=?", selectionArgs2, null);
		if (cursor == null) {
			return null;
		}
		return cursor.getString(cursor.getColumnIndexOrThrow(Thumbnails.DATA));
	}
}
